package monitoringMod.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import monitoringMod.util.DatabaseUtil;
import ttp.to.ChemicalTO;
import ttp.to.CrushTO;
import ttp.to.CuttingTO;
import ttp.to.DryingTO;
import ttp.to.TreeTO;

public class MonitoringModDAO {
	
	private DatabaseUtil dbUtil;
	private static MonitoringModDAO instance = new MonitoringModDAO();

	private MonitoringModDAO(){
		dbUtil = DatabaseUtil.getInstance();
	}
	public static MonitoringModDAO getInstance(){
		return instance;
	}
	
	public int sequenceTree() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int sequence = 0;
		String sql = "select tree_no_seq.nextval from dual";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
			sequence = rset.getInt(1);
			}
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return sequence;
	}
	
	public int sequenceCrush() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int sequence = 0;
		String sql = "select crush_no_seq.nextval from dual";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
			sequence = rset.getInt(1);
			}
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return sequence;
	}
	
	public int sequenceChemical() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int sequence = 0;
		String sql = "select chemical_no_seq.nextval from dual";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
			sequence = rset.getInt(1);
			}
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return sequence;
	}
	
	public int sequenceDrying() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int sequence = 0;
		String sql = "select drying_no_seq.nextval from dual";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
			sequence = rset.getInt(1);
			}
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return sequence;
	}
	
	public int sequenceCutting() throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		int sequence = 0;
		String sql = "select cutting_no_seq.nextval from dual";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			rset = pstmt.executeQuery();
			while(rset.next()){
			sequence = rset.getInt(1);
			}
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return sequence;
	}
	
	public int insertTree(TreeTO tto) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into trees (tree_no, enterprise, tree_type, tree_grade, tree_count, order_date, tree_check, error_rate, result) values(?,?,?,?,?,?,?,?,?)";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, tto.getTreeNo());
			pstmt.setString(2, tto.getEnterPrise());
			pstmt.setString(3, tto.getTreeType());
			pstmt.setString(4, tto.getTreeGrade());
			pstmt.setInt(5, tto.getTreeCount());
			pstmt.setString(6, tto.getOrderDate());
			pstmt.setInt(7, tto.getTreeCheck());
			pstmt.setInt(8, tto.getErrorRate());
			pstmt.setInt(9, tto.getResult());
			
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int insertCrush(CrushTO cto) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into Crush (crush_no, enterprise, tree_type, tree_grade, tree_weight, product_date, crush_check, error_rate, result) values(?,?,?,?,?,?,?,?,?)";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,  cto.getCrushNo());
			pstmt.setString(2, cto.getEnterPrise());
			pstmt.setString(3, cto.getTreeType());
			pstmt.setString(4, cto.getTreeGrade());
			pstmt.setInt(5, cto.getTreeWeight());
			pstmt.setString(6, cto.getProductDate());
			pstmt.setInt(7, cto.getCrushCheck());
			pstmt.setInt(8, cto.getErrorRate());
			pstmt.setInt(9, cto.getResult());
			
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int insertChemical(ChemicalTO cmto) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into chemical (chemical_no, enterprise, tree_type, tree_grade, pulp_weight, product_date, chemical_check, error_rate, result) values(?,?,?,?,?,?,?,?,?)";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cmto.getChemicalNo());
			pstmt.setString(2, cmto.getEnterPrise());
			pstmt.setString(3, cmto.getTreeType());
			pstmt.setString(4, cmto.getTreeGrade());
			pstmt.setInt(5, cmto.getPulpWeight());
			pstmt.setString(6, cmto.getProductDate());
			pstmt.setInt(7, cmto.getChemicalCheck());
			pstmt.setInt(8, cmto.getErrorRate());
			pstmt.setInt(9, cmto.getResult());
			
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int insertDrying(DryingTO dto) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into Drying (drying_no, enterprise, tree_type, tree_grade, paper_weight, product_date, drying_check, error_rate, result) values(?,?,?,?,?,?,?,?,?)";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, dto.getDryingNo());
			pstmt.setString(2, dto.getEnterPrise());
			pstmt.setString(3, dto.getTreeType());
			pstmt.setString(4, dto.getTreeGrade());
			pstmt.setInt(5, dto.getPaperWeight());
			pstmt.setString(6, dto.getProductDate());
			pstmt.setInt(7, dto.getDryingCheck());
			pstmt.setInt(8, dto.getErrorRate());
			pstmt.setInt(9, dto.getResult());
			
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int insertCutting(CuttingTO cuto) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "insert into trees (cutting_no, enterprise, tree_type, tree_grade, paper_count, product_date, cutting_check, error_rate, result) values(?,?,?,?,?,?,?,?,?)";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, cuto.getCuttingNo());
			pstmt.setString(2, cuto.getEnterPrise());
			pstmt.setString(3, cuto.getTreeType());
			pstmt.setString(4, cuto.getTreeGrade());
			pstmt.setInt(5, cuto.getPaperCount());
			pstmt.setString(6, cuto.getProductDate());
			pstmt.setInt(7, cuto.getCuttingCheck());
			pstmt.setInt(8, cuto.getErrorRate());
			pstmt.setInt(9, cuto.getResult());
			
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int updateTree(int treeNo) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "update trees set result=result+1 where treeNo=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, treeNo);
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int updateCrush(int CrushNo) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "update Crush set result=result+1 where CrushNo=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, CrushNo);
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int updateChemical(int ChemicalNo) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "update Chemical set result=result+1 where ChemicalNo=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, ChemicalNo);
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int updateDrying(int DryingNo) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "update Drying set result=result+1 where DryingNo=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, DryingNo);
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public int updateCutting(int CuttingNo) throws Exception{
		Connection conn = null;
		PreparedStatement pstmt = null;
		int cnt = 0;
		String sql = "update Cutting set result=result+1 where CuttingNo=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, CuttingNo);
			cnt = pstmt.executeUpdate();
		}finally{
			dbUtil.close(conn, pstmt);
		}
		return cnt;
	}
	
	public List<TreeTO> selectAllSuccessTree(int result)throws Exception{//tree 모두 검색
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<TreeTO> list = new ArrayList<TreeTO>();
		String sql = "select * from trees where result=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, result);
			rset = pstmt.executeQuery();
			while(rset.next()){
				list.add(new TreeTO(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getInt(5),
						rset.getString(6), rset.getInt(7),rset.getInt(8),rset.getInt(9)));
			}
		}finally{
			dbUtil.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public List<CuttingTO> selectAllSuccessCutting(int result) throws Exception{ // p4 - 모두 검색
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<CuttingTO> list = new ArrayList<CuttingTO>();
		String sql = "select * from cutting where result=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, result);
			rset = pstmt.executeQuery();
			while(rset.next()){
				list.add(new CuttingTO(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getInt(5),
						rset.getString(6), rset.getInt(7),rset.getInt(8),rset.getInt(9)));
			}
		}finally{
			dbUtil.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public List<CrushTO> selectAllSuccessCrush(int result) throws Exception{ // p1 - 모두 검색
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<CrushTO> list = new ArrayList<CrushTO>();
		String sql = "select * from crush where result=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, result);
			rset = pstmt.executeQuery();
			while(rset.next()){
				list.add(new CrushTO(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getInt(5),
						rset.getString(6), rset.getInt(7),rset.getInt(8),rset.getInt(9)));
			}
		}finally{
			dbUtil.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public List<ChemicalTO> selectAllSuccessChemical(int result) throws Exception{ // p2 - 모두 검색
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<ChemicalTO> list = new ArrayList<ChemicalTO>();		
		String sql = "select * from chemical where result=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, result);
			rset = pstmt.executeQuery();
			while(rset.next()){
				list.add(new ChemicalTO(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getInt(5),
						rset.getString(6), rset.getInt(7),rset.getInt(8),rset.getInt(9)));
			}
		}finally{
			dbUtil.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public List<DryingTO> selectAllSuccessDrying(int result) throws Exception{ // p3 - 모두 검색
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		List<DryingTO> list = new ArrayList<DryingTO>();
		String sql = "select * from drying where result=?";
		try{
			conn = dbUtil.getConnection();
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1, result);
			rset = pstmt.executeQuery();
			while(rset.next()){
				list.add(new DryingTO(rset.getInt(1), rset.getString(2), rset.getString(3), rset.getString(4), rset.getInt(5),
						rset.getString(6), rset.getInt(7),rset.getInt(8),rset.getInt(9)));
			}
		}finally{
			dbUtil.close(conn, pstmt, rset);
		}
		return list;
	}
	
	public int treeCountMonitoringEnterprise(String enterprise)throws Exception{
		  Connection conn = null;
		  PreparedStatement pstmt = null;
		  ResultSet rset = null;
		  int cnt = 0;
		  String sql = "select sum(tree_Count) from trees where enterprise=? ";
		  try{
		   conn = dbUtil.getConnection();
		   pstmt = conn.prepareStatement(sql);
		   pstmt.setString(1, enterprise);
		   rset = pstmt.executeQuery();
		   while(rset.next()){
		   cnt = rset.getInt(1);
		   }
		  }finally{
		   dbUtil.close(conn, pstmt, rset);
		  }
		  return cnt;
		 }
		 public int paperCountMonitoringEnterprise(String enterprise)throws Exception{
		  Connection conn = null;
		  PreparedStatement pstmt = null;
		  ResultSet rset = null;
		  int cnt = 0;
		  String sql = "select sum(paper_Count) from cutting where enterprise=?";
		  try{
		   conn = dbUtil.getConnection();
		   pstmt = conn.prepareStatement(sql);
		   pstmt.setString(1, enterprise);
		   rset = pstmt.executeQuery();
		   while(rset.next()){
		   cnt = rset.getInt(1);
		   }
		  }finally{
		   dbUtil.close(conn, pstmt, rset);
		  }
		  return cnt;
		 }
	public int treePerPageCountMonitoringTreeType(String treeType)throws Exception{
		  Connection conn = null;
		  PreparedStatement pstmt = null;
		  ResultSet rset = null;
		  int cnt = 0;
		  String sql = "select sum(paper_Count) from cutting where tree_Type=?";
		  try{
		   conn = dbUtil.getConnection();
		   pstmt = conn.prepareStatement(sql);
		   pstmt.setString(1, treeType);
		   rset = pstmt.executeQuery();
		   while(rset.next()){
		   cnt = rset.getInt(1);
		   }
		  }finally{
		   dbUtil.close(conn, pstmt, rset);
		  }
		  return cnt;
	}
}
